

CREATE TABLE "public"."Padre" (
  "IDPadre" SERIAL, 
  "DatoPadre" CHAR(18) NOT NULL, 
  CONSTRAINT "PKPadre" PRIMARY KEY("IDPadre") 
) WITHOUT OIDS;



CREATE TABLE "public"."Hijo" (
  "IDHijo" SERIAL, 
  "DatoHijo" CHAR(28) NOT NULL, 
  "IDPadre" INTEGER NOT NULL, 
  CONSTRAINT "PKHijo" PRIMARY KEY("IDHijo"), 
  CONSTRAINT "gg" FOREIGN KEY ("IDPadre")
    REFERENCES "public"."Padre"("IDPadre")
    MATCH FULL
    ON DELETE RESTRICT
    ON UPDATE CASCADE
    DEFERRABLE
    INITIALLY DEFERRED
) WITHOUT OIDS;

CREATE INDEX "fki_FKPadre-Hijo" ON "public"."Hijo"
  USING btree ("IDPadre");

CREATE OR REPLACE FUNCTION "public"."compruebaenhijo" (identificador integer) RETURNS boolean AS
$body$
/* New function body */
DECLARE
   temporal "Hijo"%ROWTYPE;

BEGIN
   SELECT INTO temporal * FROM "Hijo" WHERE "Hijo"."IDPadre"=identificador;
   IF NOT FOUND THEN
      RETURN false;
   END IF;
   RETURN true;
END
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

ALTER TABLE "Padre" ADD  CONSTRAINT "Padre_check0" CHECK (compruebaenhijo("IDPadre"));




CREATE OR REPLACE FUNCTION "public"."DisparadorHijoBorradoCMin1" () RETURNS trigger AS
$body$
DECLARE
   temporal INTEGER;

BEGIN

   SELECT INTO temporal count(*) FROM "Hijo" WHERE "Hijo"."IDPadre"=OLD."IDPadre";
   RAISE NOTICE 'number of rows %', temporal;


   IF temporal < 1 THEN
      RAISE EXCEPTION 'ERROR DE BORRADO';
      RETURN NULL;
   END IF;

   RETURN NULL;
END
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;


CREATE TRIGGER "BorradoCMin1" AFTER DELETE 
ON "public"."Hijo" FOR EACH ROW 
EXECUTE PROCEDURE "public"."DisparadorHijoBorradoCMin1"();


begin;

insert into "Hijo" values (1,'dd',1);

insert into "Hijo" values (2,'dd',1);

insert into "Hijo" values (3,'dd',1);

insert into "Padre" values (1,'pp');

commit;


-- the error comes here!!
-- for every row, the trigger gets executed, 
-- but always returns that there are 3 rows, 
-- the same that were before executing DELETE.

delete from "Hijo";

